package com.one.user.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.one.user.dao.UserDao;
import com.one.user.pojo.User;
import com.one.util.DBUtil;

public class UserDaoImpl implements UserDao{

	@Override
	public List<User> selectAll() {
		List<User> list = new ArrayList<>();
		Connection conn = DBUtil.getConnection();
		String sql = "select * from user where flag = 1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setAge(rs.getInt("age"));
				u.setGender(rs.getString("gender"));
				u.setRemake(rs.getString("remake"));
				list.add(u);
				u = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(conn, ps, rs);
		}
		return list;
	}

	
	
	public User selectByName(String username) {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from user where username = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		System.out.println("Dao - username: " + username);
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			if(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setAge(rs.getInt("age"));
				u.setGender(rs.getString("gender"));
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}



	@Override
	public int addUser(String username, String password, int age, String gender,String remake) {
		Connection conn = DBUtil.getConnection();
		String sql = "INSERT INTO `user` (`username`, `password`, `age`, `gender`, `remake`, `flag`) VALUES (?, ?, ?, ?, ?, ?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setInt(3, age);
			ps.setString(4, gender);
			ps.setString(5, remake);
			ps.setInt(6, 1);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(conn, ps, null);
		}
		return n;
	}



	@Override
	public int updateUser(int id, String username, String password, int age, String gender,String remake) {
		Connection conn = DBUtil.getConnection();
		String sql = "UPDATE `user` SET `username`=?, `password`=?,"
				+ " `age`=?, `gender`=?, `remake`=? WHERE (`id`=?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setInt(3, age);
			ps.setString(4, gender);
			ps.setString(5, remake);
			ps.setInt(6, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(conn, ps, null);
		}
		return n;
	
	}



	@Override
	public int deleteUser(int id) {
		Connection conn = DBUtil.getConnection();
		String sql = "update user set flag=0 where id=?";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(conn, ps, null);
		}
		return n;
	}



	@Override
	public User selectById(int id) {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from user where id = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		System.out.println("Dao - username: " + id);
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setAge(rs.getInt("age"));
				u.setGender(rs.getString("gender"));
				u.setRemake(rs.getString("remake"));
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	
	}

}
